package com.mayn.alibaba;

import java.util.ArrayList;

import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.mayn.common.JfinalInit;
import com.mayn.util.DateUtil;
import com.mayn.util.HttpUtil;
import com.mayn.util.RegUtil;
import com.mayn.util.XsoupUtils;

/**
 * 阿里巴巴国际站数据采集分析
 * 
 * @author Administrator
 *
 */
public class AlibabaAction {

	private static final Logger LOG = LoggerFactory.getLogger(AlibabaAction.class);

	public static void main(String[] args) {
		
		JfinalInit.InitDb(false, null);
		
		getCategoryByProducts();
		
		
	}
	
	public static Boolean getCategoryByProducts() {
		Boolean ret =false;
		try{
			String url = "http://www.alibaba.com/Products";
			HashMap<String, String> headers = new HashMap<String, String>();
			headers.put("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8");
			headers.put("Accept-Encoding", "gzip, deflate, sdch");
			headers.put("Accept-Language", "zh-CN,zh;q=0.8");
			headers.put("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3050.3 Safari/537.36");
			String content = HttpUtil.sendGetWithURLConnection(url, null, headers, "UTF-8");
	
			List<LinkedHashMap<String, String>> list = getCategoryByProducts(content);
			for (int i = 0; i < list.size(); i++) {
				HashMap<String, String> map = new  HashMap<String, String>();
				map.put("APC_PID", list.get(i).get("pId"));
				map.put("APC_PURL", getAlibabaUrl(list.get(i).get("pUrl")));
				map.put("APC_PNAME", list.get(i).get("pName"));
				map.put("APC_PNUM", list.get(i).get("pNum"));
				map.put("APC_FID", list.get(i).get("fId"));
				saveAlibabaProductCategory(map);
				LOG.info("类目数据写入成功:" + "" + map.get("APC_PID") + "--" + map.get("APC_PURL") + "--" + map.get("APC_PNAME") + "--" + map.get("APC_PNUM") + "--" + map.get("APC_FID"));
				
				if(list.get(i).get("fId").equals("0")){
					map = new  HashMap<String, String>();
					String today = DateUtil.dateFmtToString(new Date(), "yyyyMMdd");
					map.put("APCH_DAY", today);
					map.put("APCH_PID", list.get(i).get("pId"));
					map.put("APCH_PNUM", list.get(i).get("pNum"));
					saveAlibabaProductCategoryHistory(map);
				}
			}
			getLevelTwoPnum();
			return true;
			
		}catch(Exception e){
			
		}
		return ret;
		
	}
	
	
	/**
	 * 二级类目数据采集
	 */
	public static void getLevelTwoPnum(){
		try{
			String countSql = "SELECT COUNT(*) FROM alibaba_product_category WHERE APC_DEL=0 AND APC_FID>0 AND APC_SYNC = 0";
			String selectSql = "SELECT APC_ID,APC_PID,APC_PURL FROM alibaba_product_category WHERE APC_DEL=0 AND APC_FID>0 AND APC_SYNC = 0";
			String updateSql = "UPDATE alibaba_product_category SET APC_SYNC=1 WHERE APC_DEL=0 AND APC_ID = ?";
			String initSql = "UPDATE alibaba_product_category SET APC_SYNC=0 WHERE APC_DEL=0";
			
			int c= Db.queryLong(countSql).intValue();
			if(c ==0){
				Db.update(initSql);
				c= Db.queryLong(countSql).intValue();
				LOG.info("二级类目初始化采集" );
			}
			if(c > 0){
				List<Record> list = Db.find(selectSql);
				if(list.size()>0){
					for(int i=0;i<list.size();i++){
						Record rec = list.get(i);
						String id =  rec.getStr("APC_ID");
						String pId = rec.getStr("APC_PID");
						String pUrl = getAlibabaUrl(rec.getStr("APC_PURL"));
						int pNum = getLevelTwoPnum(pUrl);
						HashMap<String, String> map = new  HashMap<String, String>();
						map.put("APC_PID", pId);
						map.put("APC_PNUM", String.valueOf(pNum));
						saveAlibabaProductCategoryPnum(map);
						Db.update(updateSql, id);
						
						map = new  HashMap<String, String>();
						String today = DateUtil.dateFmtToString(new Date(), "yyyyMMdd");
						map.put("APCH_DAY", today);
						map.put("APCH_PID", pId);
						map.put("APCH_PNUM", String.valueOf(pNum));
						saveAlibabaProductCategoryHistory(map);
						
						LOG.info("二级类目采集成功:" + pUrl);
					}
				}
			}else{
				LOG.error("二级类目无法采集");
			}
		}catch(Exception e){
			LOG.error("产品二级类目产品数量采集失败:" + e.getMessage());
		}
	}
	
	/**
	 * 二级类目产品数量获取
	 * @param pUrl
	 * @return
	 */
	public static int getLevelTwoPnum(String pUrl){
		int ret = 0;
		try{
			HashMap<String, String> headers = new HashMap<String, String>();
			headers.put("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8");
			headers.put("Accept-Encoding", "gzip, deflate, sdch");
			headers.put("Accept-Language", "zh-CN,zh;q=0.8");
			headers.put("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3050.3 Safari/537.36");
			String content = HttpUtil.sendGetWithURLConnection(pUrl, null, headers, "UTF-8",true);
			String regex = "//div[@class='ui2-breadcrumb ui2-breadcrumb-normal']/outerHtml()";
			String divText = XsoupUtils.GetXsoupStr(content, regex);
			if(StrKit.notBlank(divText)){
				regex = "//div/span/outerHtml()";
				List<String> listSpan = XsoupUtils.GetXsoupList(divText, regex);
				if(listSpan.size()>0){
					if(listSpan.size()>6 ){
						String numText = listSpan.get(listSpan.size()-2);
						String resultText = listSpan.get(listSpan.size()-1);
						if(resultText.equals("<span>Results</span>")){
							regex = "//span/text()";
							numText = XsoupUtils.GetXsoupStr(numText, regex);
							if(numText.contains(",")){
								numText = numText.replace(",", "");
							}
							ret = Integer.parseInt(numText);
						}
						//以下为验证上级类目是否准确，可删除
						regex = "//span/a/@href";
						
						//当前类目
						String spanText4 = listSpan.get(listSpan.size()-4);
						String spanText4Url = XsoupUtils.GetXsoupStr(spanText4, regex);
						int spanText4UrlPid = getPidFromUrl(spanText4Url);
						//上级类目
						String spanText6 = listSpan.get(listSpan.size()-6);
						String spanText6Url = XsoupUtils.GetXsoupStr(spanText6, regex);
						int spanText6UrlPid = getPidFromUrl(spanText6Url);
						
						int pId = getPidFromUrl(pUrl);
						if(pId == spanText4UrlPid){
							int fId = Db.queryInt("SELECT APC_FID FROM alibaba_product_category WHERE APC_DEL=0 AND APC_PID=?",pId);
							if(fId != spanText6UrlPid){
								if(listSpan.size()>8){
									String spanText8 = listSpan.get(listSpan.size()-8);
									String spanText8Url = XsoupUtils.GetXsoupStr(spanText8, regex);
									int spanText8UrlPid = getPidFromUrl(spanText8Url);
									if(fId != spanText8UrlPid){
										LOG.error("上级类目验证不准确！" + pUrl);
									}
								}
							}
						}else{
							LOG.error("未知错误");
						}
					}else{
						for(int i=0;i<listSpan.size();i++){
							regex = "//span/allText()";
							LOG.info(XsoupUtils.GetXsoupStr(listSpan.get(i), regex));
						}
					}
				}
				else{
					LOG.error("二级类目导航标签span找不到:" + regex);
				}
			}else{
				LOG.error("二级类目导航标签找不到:" + regex);
			}
		}catch(Exception e){
			LOG.error("二级类目产品数量下载失败:" + e.getMessage());
		}
		return ret;
		
	}
	
	
	
	public static List<LinkedHashMap<String, String>> getCategoryByProducts(String content) {
		List<LinkedHashMap<String, String>> ret = new ArrayList<LinkedHashMap<String, String>>();
		try {
			String title = XsoupUtils.GetXsoupStr(content, "//title/text()");
			if (title.equals("Manufacturers, Suppliers and Exporters Directory on Alibaba.com")) {
				//先下载一级类目
				String regex = "//div[@class='sub-item']/outerHtml()";
				List<String> listOne = XsoupUtils.GetXsoupList(content, regex);
				if (listOne.size() > 0) {
					for (int i = 0; i < listOne.size(); i++) {
						String textOne = listOne.get(i);
						regex = "//h4[@class='sub-title']/a/@href";
						String pUrl = XsoupUtils.GetXsoupStr(textOne, regex);
						int pId =getPidFromUrl(pUrl);
						regex = "//h4[@class='sub-title']/a/text()";
						String pName = XsoupUtils.GetXsoupStr(textOne, regex);
						regex = "//h4[@class='sub-title']/span/text()";
						String pNum = XsoupUtils.GetXsoupStr(textOne, regex);
						pNum = pNum.replace("(", "");
						pNum = pNum.replace(")", "");
						LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
						map.put("pId", String.valueOf(pId));
						map.put("pUrl",pUrl);
						map.put("pName",pName);
						map.put("pNum",pNum);
						map.put("fId","0");
						ret.add(map);
						LOG.debug("产品一级类目:" + map.get("pId") + "--" + map.get("pUrl") + "--" + map.get("pName") + "--" + map.get("pNum") + "--" + map.get("fId") );
						//再下载二级类目
						List<LinkedHashMap<String, String>> listTwo = getLevelTwoCategoryByProducts(textOne);
						if(listTwo.size()>0){
							for(int j=0;j<listTwo.size();j++){
								map = new LinkedHashMap<String, String>();
								map.put("pId", listTwo.get(j).get("pId"));
								map.put("pUrl",listTwo.get(j).get("pUrl"));
								map.put("pName",listTwo.get(j).get("pName"));
								map.put("pNum","0");
								map.put("fId",String.valueOf(pId));
								ret.add(map);
								LOG.debug("产品二级类目:" + map.get("pId") + "--" + map.get("pUrl") + "--" + map.get("pName") + "--" + map.get("pNum") + "--" + map.get("fId") );
							}
						}else{
							LOG.error("找不到二级类目:" + pUrl);
						}
					}
				}
			}
		} catch (Exception e) {
			LOG.error("产品一级类目下载失败:" + e.getMessage());
		}
		return ret;
	}
	
	/**
	 * 产品一级分类
	 * 
	 * @return
	 */
	public static List<LinkedHashMap<String, String>> getLevelOneCategoryByProducts(String content) {
		List<LinkedHashMap<String, String>> ret = new ArrayList<LinkedHashMap<String, String>>();
		try {
			String title = XsoupUtils.GetXsoupStr(content, "//title/text()");
			if (title.equals("Manufacturers, Suppliers and Exporters Directory on Alibaba.com")) {
				String regex = "//h4[@class='sub-title']/a/outerHtml()";
				List<String> list = XsoupUtils.GetXsoupList(content, regex);
				if (list.size() > 0) {
					for (int i = 0; i < list.size(); i++) {
						LOG.info("产品一级类目:" + list.get(i));
					}
				}
			}
		} catch (Exception e) {
			LOG.error("产品一级类目下载失败:" + e.getMessage());
		}
		return ret;
	}

	public static List<LinkedHashMap<String, String>> getLevelTwoCategoryByProducts(String content) {
		List<LinkedHashMap<String, String>> ret = new ArrayList<LinkedHashMap<String, String>>();
		try {
			String regex = "//div[@class='sub-item-cont-wrapper']/ul/li/outerHtml()";
			List<String> list = XsoupUtils.GetXsoupList(content, regex);
			if (list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {
					String text = list.get(i);
					regex = "//a/@href";
					String pUrl = XsoupUtils.GetXsoupStr(text, regex);
					int pId =getPidFromUrl(pUrl);
					regex = "//a/text()";
					String pName = XsoupUtils.GetXsoupStr(text, regex);
					LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
					map.put("pId", String.valueOf(pId));
					map.put("pUrl",pUrl);
					map.put("pName",pName);
					ret.add(map);
				}
			}

		} catch (Exception e) {
			LOG.error("产品二级类目下载失败:" + e.getMessage());
		}
		return ret;
	}

	public static List<String> getTopProductsByCategory(String content) {
		try {
			String title = XsoupUtils.GetXsoupStr(content, "//title/text()");
			if (title.equals("Manufacturers, Suppliers and Exporters Directory on Alibaba.com")) {
				String regex = "//table[@class='cg-nav-wrapper cg-nav-wrapper-row-2']/tbody//td/a/span/text()";
				List<String> list = XsoupUtils.GetXsoupList(content, regex);
				if (list.size() > 0) {
					for (int i = 0; i < list.size(); i++) {
						LOG.info("产品折叠大类:" + list.get(i));
					}
				}
				return list;
			}
		} catch (Exception e) {
			LOG.error("产品折叠大类下载失败:" + e.getMessage());
		}
		return null;
	}
	
	/**通过url获取类目ID
	 * 
	 * @param url http://www.alibaba.com/Agriculture_p1
	 * @return
	 */
	public static int getPidFromUrl(String url){
		try{
			if(url.contains("_pid")){
				String regex= "(?:_pid)([\\d]*)";
				String idText = RegUtil.GetRegStr(url, regex, 1);
				int id = Integer.parseInt(idText);
				return id;
			}
			if(url.contains("_p")){
				String regex= "(?:_p)([\\d]*)";
				String idText = RegUtil.GetRegStr(url, regex, 1);
				int id = Integer.parseInt(idText);
				return id;
			}
		}catch(Exception e){
			LOG.error("类目ID获取失败:" + e.getMessage());
		}
		return 0;
	}
	

	
	public static String getAlibabaUrl(String url){
		if(url.contains("’")){
			url = url.replace("’", "-");
		}
		if(url.contains("'")){
			url = url.replace("’", "-");
		}
		if(url.contains("-/-")){
			url = url.replace("-/-", "--");
		}
		if(url.contains("--")){
			url = url.replace("--", "-");
		}
		return url;
	}
	
	/**
	 * alibaba_product_category表插入数据
	 * 
	 * @param map
	 * @return
	 */
	public static Boolean saveAlibabaProductCategory(HashMap<String, String> map){
		String checkSql = "SELECT COUNT(*) FROM alibaba_product_category WHERE APC_DEL=0 AND APC_PID=? AND APC_FID = ?";
		String insertSql = "INSERT INTO alibaba_product_category(APC_PID,APC_PURL,APC_PNAME,APC_PNUM,APC_FID,APC_DEL,APC_CREATE_DATETIME) VALUES (?,?,?,?,?,0,now())";
		String updateSql = "UPDATE alibaba_product_category SET APC_PURL=?,APC_PNAME=?,APC_PNUM=?,APC_UPDATE_DATETIME=now() where APC_DEL=0 AND APC_PID=? AND APC_FID=?";
		int c = Db.queryLong(checkSql, map.get("APC_PID"), map.get("APC_FID")).intValue();
		if (c == 0) {
			Db.update(insertSql,map.get("APC_PID"),map.get("APC_PURL"),map.get("APC_PNAME"),map.get("APC_PNUM"),map.get("APC_FID"));
			return true;
		}else if(c==1){
			Db.update(updateSql,map.get("APC_PURL"),map.get("APC_PNAME"),map.get("APC_PNUM"),map.get("APC_PID"),map.get("APC_FID"));
			return true;
		}else{
			LOG.error("alibaba_product_category表有重复记录");
		}
		return false;
	}
	
	public static Boolean saveAlibabaProductCategoryPnum(HashMap<String, String> map){
		String checkSql = "SELECT COUNT(*) FROM alibaba_product_category WHERE APC_DEL=0 AND APC_PID=?";
		String updateSql = "UPDATE alibaba_product_category SET APC_PNUM=? WHERE APC_DEL=0 AND APC_PID=?";
		int c = Db.queryLong(checkSql, map.get("APC_PID")).intValue();
		if (c == 0) {
			return false;
		}else if(c==1){
			Db.update(updateSql,map.get("APC_PNUM"),map.get("APC_PID"));
			return true;
		}else{
			LOG.error("alibaba_product_category表有重复记录");
		}
		return false;
	}
	
	public static Boolean saveAlibabaProductCategoryHistory(HashMap<String, String> map){
		String checkSql = "SELECT COUNT(*) FROM alibaba_product_category_history WHERE APCH_DEL=0 AND APCH_DAY=? AND APCH_PID = ?";
		String insertSql = "INSERT INTO alibaba_product_category_history(APCH_DAY,APCH_PID,APCH_PNUM,APCH_DEL,APCH_CREATE_DATETIME) VALUES (?,?,?,0,now())";
		String updateSql = "UPDATE alibaba_product_category_history SET APCH_PNUM=?,APCH_UPDATE_DATETIME=now() where APCH_DEL=0 AND APCH_DAY=? AND APCH_PID=?";
		int c = Db.queryLong(checkSql, map.get("APC_PID"), map.get("APC_FID")).intValue();
		if (c == 0) {
			Db.update(insertSql,map.get("APCH_DAY"),map.get("APCH_PID"),map.get("APCH_PNUM"));
			return true;
		}else if(c==1){
			Db.update(updateSql,map.get("APCH_PNUM"),map.get("APCH_DAY"),map.get("APCH_PID"));
			return true;
		}else{
			LOG.error("alibaba_product_category_history表有重复记录");
		}
		return false;
	}

}
